#!/bin/bash
# Copyright (C) 2019 Checkmk GmbH - License: GNU General Public License v2
# This file is part of Checkmk (https://checkmk.com). It is subject to the terms and
# conditions defined in the file COPYING, which is part of this source code package.

# Checkmk agent plugin for monitoring ORACLE databases

# Get list of all running databases
SIDS=$(UNIX95=true ps ax -o args | sed -n '/^\(ora\|xe\)_pmon_\([^ ]*\)/s//\2/p')
if [ -z "$SIDS" ]; then
    # If on this system we've already found a database
    if [ -e "$MK_CONFDIR/mk_oracle.found" ]; then
        echo '<<<oracle_version>>>'
        echo '<<<oracle_sessions>>>'
        echo '<<<oracle_logswitches>>>'
        echo '<<<oracle_tablespaces>>>'
    fi
    exit 0
fi

touch "$MK_CONFDIR"/mk_oracle.found

# Recreate data if cachefile is older than 120 seconds.
# If you set this to 0, then the cache file will be created
# as often as possible. If the database queries last longer
# then your check interval, caching will be active nevertheless.
CACHE_MAXAGE=120

# Source the optional configuration file for this agent plugin
if [ -e "$MK_CONFDIR/mk_oracle.cfg" ]; then
    # shellcheck source=../../../agents/cfg_examples/mk_oracle.cfg
    . "$MK_CONFDIR"/mk_oracle.cfg
fi

# You can specify a list of SIDs to monitor. Those databases will
# only be handled, if they are found running, though!
#
#   ONLY_SIDS="XE HIRN SEPP"
#
# It is possible to filter SIDS negatively. Just add the following to
# the mk_oracle.cfg file:
#
#   EXCLUDE_<sid>="ALL"
#
# Another option is to filter single checks for SIDS. Just add
# lines as follows to the mk_oracle.cfg file. One service per
# line:
#
#   EXCLUDE_<sid>="<service>"
#
# For example skip oracle_sessions and oracle_logswitches checks
# for the instance "mysid".
#
#   EXCLUDE_mysid="sessions logswitches"
#
#
# This check uses a cache file to prevent problems with long running
# SQL queries. It starts building a cache when
#   a) no cache is present or the cache is too old and
#   b) the cache is not currently being built
# The cache is used for $CACHE_MAXAGE seconds. The CACHE_MAXAGE
# option is pre-set to 120 seconds but can be changed in mk_oracle.cfg.

sqlplus() {
    if OUTPUT=$({
        echo 'set pages 0'
        echo 'whenever sqlerror exit 1'
        echo 'set lines 8000'
        echo 'set feedback off'
        cat
    } | "$MK_CONFDIR"/sqlplus.sh "$1"); then
        echo "${OUTPUT}" | sed -e 's/[[:space:]]\+/ /g' -e '/^[[:space:]]*$/d' -e "s/^/$1 /"
    else
        # shellcheck disable=SC2001 # stylistic false positive
        echo "${OUTPUT}" | sed "s/^/$1 FAILURE /"
    fi
}

for SID in $SIDS; do
    # Check if SID is listed in ONLY_SIDS if this is used
    if [ "$ONLY_SIDS" ]; then
        SKIP=yes
        for S in $ONLY_SIDS; do
            if [ "$S" = "$SID" ]; then
                SKIP=
                break
            fi
        done
        if [ "$SKIP" ]; then continue; fi
    fi

    EXCLUDE=EXCLUDE_$SID
    EXCLUDE=${!EXCLUDE}
    # SID filtered totally?
    if [ "$EXCLUDE" = "ALL" ]; then
        continue
    fi

    # Do Version-Check (use as a general login check) without caching
    if [ "$EXCLUDE" = "${EXCLUDE/version/}" ]; then
        echo '<<<oracle_version>>>'
        echo "select banner from v\$version where banner like 'Oracle%';" | sqlplus "$SID"
    fi

    CACHE_FILE=$MK_CONFDIR/oracle_$SID.cache

    # Check if file exists and recent enough
    CACHE_FILE_UPTODATE=
    if [ -s "$CACHE_FILE" ]; then
        NOW=$(date +%s)
        MTIME=$(stat -c %Y "$CACHE_FILE")
        if [ $((NOW - MTIME)) -le $CACHE_MAXAGE ]; then
            CACHE_FILE_UPTODATE=1
        fi
    fi

    # If the cache file exists, output it, regardless of its age. If it's outdated
    # then it will be recreated *asynchronously*. It's new contents will not
    # be available here anyway.
    if [ -s "$CACHE_FILE" ]; then cat "$CACHE_FILE"; fi

    # When the cache file is not valid, we recreated it, but only if there is not
    # yet a background process from a previous check still doing this! We see this
    # because of the existance of the .new file
    # When the cache is old and there is no *new file present, then start a query
    # to update the information for this instance.
    if [ -z "$CACHE_FILE_UPTODATE" ] && [ ! -e "$CACHE_FILE.new" ]; then
        setsid bash -c "
            set -o noclobber
            function sqlplus ()
            {
                if OUTPUT=\$({ echo 'set pages 0' ; echo 'whenever sqlerror exit 1'; echo 'set lines 8000' ; echo 'set feedback off'; cat ; } | $MK_CONFDIR/sqlplus.sh \$1)
		then
                    echo \"\${OUTPUT}\" | sed -e 's/[[:space:]]\+/ /g' -e '/^[[:space:]]*$/d' -e \"s/^/\$1 /\"
		else
		    echo \"\${OUTPUT}\" | sed \"s/^/\$1 FAILURE /\"
                fi
            }

            {
            # Only execute checks when not filtered
            if [ '$EXCLUDE' = '${EXCLUDE/sessions/}' ]; then
                echo '<<<oracle_sessions>>>'
                echo \"select count(1) from v\\\$session where status = 'ACTIVE';\" | sqlplus \"$SID\"
            fi

            if [ '$EXCLUDE' = '${EXCLUDE/logswitches/}' ]; then
                echo '<<<oracle_logswitches>>>'
                echo \"select count(1) from v\\\$loghist where first_time > sysdate - 1/24;\" | sqlplus \"$SID\"
            fi

            if [ '$EXCLUDE' = '${EXCLUDE/jobs/}' ]; then
                echo '<<<oracle_jobs>>>'
                sqlplus \"$SID\" <<EOF
                SELECT j.OWNER, j.JOB_NAME, j.STATE,
                (TRUNC(sysdate) + j.LAST_RUN_DURATION - TRUNC(sysdate)) * 86400 AS LAST_RUN_DURATION,
                j.RUN_COUNT, j.ENABLED, NVL(j.NEXT_RUN_DATE, to_date('1970-01-01', 'YYYY-mm-dd')),
                NVL(j.SCHEDULE_NAME, '-'), d.STATUS
                FROM dba_scheduler_jobs j, dba_scheduler_job_run_details d
                WHERE d.owner=j.OWNER AND d.JOB_NAME=j.JOB_NAME
                    AND d.LOG_ID=(SELECT max(LOG_ID) FROM dba_scheduler_job_run_details dd
                                  WHERE dd.owner=j.OWNER and dd.JOB_NAME=j.JOB_NAME);
EOF
            fi

            if [ '$EXCLUDE' = '${EXCLUDE/rman_backups/}' ]; then
                echo '<<<oracle_rman_backups>>>'
                sqlplus \"$SID\" <<EOF
                SELECT b.COMMAND_ID, a.STATUS,
                to_char(a.START_TIME, 'YYYY-mm-dd_HH24:MI:SS') AS START_TIME,
                to_char(a.END_TIME, 'YYYY-mm-dd_HH24:MI:SS') AS END_TIME, b.INPUT_TYPE
                FROM SYS.V_\\\$RMAN_BACKUP_JOB_DETAILS a,
                (SELECT input_type, max(command_id) as command_id
                 FROM SYS.V_\\\$RMAN_BACKUP_JOB_DETAILS WHERE STATUS<>'RUNNING' GROUP BY input_type) b
                WHERE a.COMMAND_ID = b.COMMAND_ID;
EOF
            fi

            if [ '$EXCLUDE' = '${EXCLUDE/tablespaces/}' ]; then
                echo '<<<oracle_tablespaces>>>'
                sqlplus \"$SID\" <<EOF | sed 's/READ ONLY/READONLY/g'
                select f.file_name, f.tablespace_name, f.status, f.AUTOEXTENSIBLE,
                f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY,
                f.ONLINE_STATUS, t.BLOCK_SIZE, t.status, decode(sum(fs.blocks), NULL, 0,
                sum(fs.blocks)) free_blocks
                from dba_data_files f, dba_tablespaces t, dba_free_space fs
                where f.tablespace_name = t.tablespace_name
                and f.file_id = fs.file_id(+)
                group by f.file_name, f.tablespace_name, f.status, f.autoextensible,
                f.blocks, f.maxblocks, f.user_blocks, f.increment_by, f.online_status,
                t.block_size, t.status
                UNION
                select f.file_name, f.tablespace_name, f.status, f.AUTOEXTENSIBLE,
                f.blocks, f.maxblocks, f.USER_BLOCKS, f.INCREMENT_BY, 'TEMP',
                t.BLOCK_SIZE, t.status, sum(sh.blocks_free) free_blocks
                from v\\\$thread th, dba_temp_files f, dba_tablespaces t, v\\\$temp_space_header sh
                WHERE f.tablespace_name = t.tablespace_name and f.file_id = sh.file_id
                GROUP BY th.instance, f.file_name, f.tablespace_name, f.status,
                f.autoextensible, f.blocks, f.maxblocks, f.user_blocks, f.increment_by,
                'TEMP', t.block_size, t.status;
EOF
            fi
            } > $CACHE_FILE.new && mv $CACHE_FILE.new $CACHE_FILE || rm -f $CACHE_FILE*
        "
    fi
done
